{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas DataFrame 常用函数练习\n",
    "# Pandas DataFrame Common Functions Practice\n",
    "\n",
    "## 📝 练习目标 Practice Objectives\n",
    "\n",
    "通过本练习，你将掌握：\n",
    "- 数据读取和基本操作\n",
    "- 数据探索和统计分析\n",
    "- 列操作和数据转换\n",
    "\n",
    "Through this practice, you will master:\n",
    "- Data reading and basic operations\n",
    "- Data exploration and statistical analysis\n",
    "- Column operations and data transformation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 🏃‍♂️ 准备工作 Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导入必要的库\n",
    "# Import necessary libraries\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# 设置显示选项，让数据更好看\n",
    "# Set display options for better visibility\n",
    "pd.set_option('display.max_columns', 10)\n",
    "pd.set_option('display.width', 1000)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 📊 创建示例数据 Creating Sample Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建学生成绩数据\n",
    "# Create student grade data\n",
    "student_data = {\n",
    "    '学生姓名': ['张三', '李四', '王五', '赵六', '钱七', '孙八', '周九', '吴十'],\n",
    "    '数学成绩': [85, 92, 78, 65, 88, 75, 95, 82],\n",
    "    '英语成绩': [90, 85, 82, 70, 95, 78, 88, 86],\n",
    "    '语文成绩': [88, 90, 75, 68, 85, 72, 92, 80],\n",
    "    '出勤率': [0.95, 1.0, 0.88, 0.92, 0.98, 0.85, 0.96, 0.90],\n",
    "    '作业完成率': [0.90, 0.95, 0.85, 0.88, 0.92, 0.80, 0.94, 0.87]\n",
    "}\n",
    "\n",
    "df = pd.DataFrame(student_data)\n",
    "print(\"学生数据:\")\n",
    "print(\"Student Data:\")\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 🔍 练习1：基础操作 Basic Operations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 任务1.1：数据预览 Data Preview\n",
    "\n",
    "显示DataFrame的前3行数据\n",
    "\n",
    "Show the first 3 rows of the DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 在这里写你的代码\n",
    "# Write your code here\n",
    "df.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 任务1.2：数据信息 Data Information\n",
    "\n",
    "查看DataFrame的基本信息\n",
    "\n",
    "Check the basic information of the DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 在这里写你的代码\n",
    "# Write your code here\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 任务1.3：统计描述 Statistical Description\n",
    "\n",
    "获取数值列的统计描述\n",
    "\n",
    "Get statistical description of numeric columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 在这里写你的代码\n",
    "# Write your code here\n",
    "df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 🧮 练习2：列操作 Column Operations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 任务2.1：计算总分 Calculate Total Score\n",
    "\n",
    "创建一个\"总分\"列，计算所有科目的总分\n",
    "\n",
    "Create a \"Total Score\" column, calculating the sum of all subjects"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 在这里写你的代码\n",
    "# Write your code here\n",
    "df['总分'] = df['数学成绩'] + df['英语成绩'] + df['语文成绩']\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 任务2.2：计算平均分 Calculate Average Score\n",
    "\n",
    "创建一个\"平均分\"列，计算所有科目的平均分\n",
    "\n",
    "Create an \"Average Score\" column, calculating the average of all subjects"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 在这里写你的代码\n",
    "# Write your code here\n",
    "df['平均分'] = df['总分'] / 3\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 任务2.3：创建成绩等级 Create Grade Level\n",
    "\n",
    "根据平均分创建成绩等级：\n",
    "- 90分以上：优秀\n",
    "- 80-89分：良好\n",
    "- 70-79分：中等\n",
    "- 60-69分：及格\n",
    "- 60分以下：不及格\n",
    "\n",
    "Create grade level based on average score:\n",
    "- 90+: Excellent\n",
    "- 80-89: Good\n",
    "- 70-79: Average\n",
    "- 60-69: Pass\n",
    "- Below 60: Fail"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 在这里写你的代码\n",
    "# Write your code here\n",
    "def get_grade_level(score):\n",
    "    if score >= 90:\n",
    "        return '优秀'\n",
    "    elif score >= 80:\n",
    "        return '良好'\n",
    "    elif score >= 70:\n",
    "        return '中等'\n",
    "    elif score >= 60:\n",
    "        return '及格'\n",
    "    else:\n",
    "        return '不及格'\n",
    "\n",
    "df['成绩等级'] = df['平均分'].apply(get_grade_level)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 任务2.4：综合表现计算 Comprehensive Performance\n",
    "\n",
    "创建一个\"综合表现\"列，使用以下权重计算：\n",
    "- 平均分权重：60%\n",
    "- 出勤率权重：20%\n",
    "- 作业完成率权重：20%\n",
    "\n",
    "Create a \"Comprehensive Performance\" column using the following weights:\n",
    "- Average score weight: 60%\n",
    "- Attendance rate weight: 20%\n",
    "- Assignment completion rate weight: 20%"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 在这里写你的代码\n",
    "# Write your code here\n",
    "df['综合表现'] = (df['平均分'] * 0.6 + \n",
    "                df['出勤率'] * 100 * 0.2 + \n",
    "                df['作业完成率'] * 100 * 0.2)\n",
    "df[['学生姓名', '平均分', '综合表现']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 📈 练习3：数据分析 Data Analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 任务3.1：最高分学生 Top Student\n",
    "\n",
    "找出总分最高的学生\n",
    "\n",
    "Find the student with the highest total score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 在这里写你的代码\n",
    "# Write your code here\n",
    "top_student = df.loc[df['总分'].idxmax()]\n",
    "print(f\"最高分学生: {top_student['学生姓名']}，总分: {top_student['总分']}\")\n",
    "print(f\"Top student: {top_student['学生姓名']}, Total score: {top_student['总分']}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 任务3.2：科目平均分 Subject Averages\n",
    "\n",
    "计算各科目的平均分\n",
    "\n",
    "Calculate the average score for each subject"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 在这里写你的代码\n",
    "# Write your code here\n",
    "subject_averages = df[['数学成绩', '英语成绩', '语文成绩']].mean()\n",
    "print(\"各科目平均分:\")\n",
    "print(\"Subject Averages:\")\n",
    "subject_averages"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 任务3.3：成绩等级统计 Grade Level Statistics\n",
    "\n",
    "统计各成绩等级的学生人数\n",
    "\n",
    "Count the number of students in each grade level"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 在这里写你的代码\n",
    "# Write your code here\n",
    "grade_counts = df['成绩等级'].value_counts()\n",
    "print(\"成绩等级分布:\")\n",
    "print(\"Grade Level Distribution:\")\n",
    "print(grade_counts)\n",
    "\n",
    "# 绘制饼图（可选）\n",
    "# Optional: Draw pie chart\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "plt.figure(figsize=(8, 6))\n",
    "plt.pie(grade_counts.values, labels=grade_counts.index, autopct='%1.1f%%')\n",
    "plt.title('成绩等级分布 / Grade Level Distribution')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 任务3.4：相关性分析 Correlation Analysis\n",
    "\n",
    "分析各成绩指标之间的相关性\n",
    "\n",
    "Analyze the correlation between different performance indicators"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 在这里写你的代码\n",
    "# Write your code here\n",
    "numeric_columns = ['数学成绩', '英语成绩', '语文成绩', '总分', '平均分', '出勤率', '作业完成率', '综合表现']\n",
    "correlation_matrix = df[numeric_columns].corr()\n",
    "print(\"相关性矩阵:\")\n",
    "print(\"Correlation Matrix:\")\n",
    "correlation_matrix\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 🎯 挑战题目 Challenge Problems"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 挑战1：找出各科最高分学生\n",
    "Challenge 1: Find top students in each subject"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 在这里写你的代码\n",
    "# Write your code here\n",
    "subjects = ['数学成绩', '英语成绩', '语文成绩']\n",
    "for subject in subjects:\n",
    "    top_student = df.loc[df[subject].idxmax()]\n",
    "    print(f\"{subject} 最高分: {top_student['学生姓名']} ({top_student[subject]}分)\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 挑战2：创建排名列\n",
    "Challenge 2: Create ranking columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 在这里写你的代码\n",
    "# Write your code here\n",
    "df['总分排名'] = df['总分'].rank(ascending=False, method='min').astype(int)\n",
    "df['综合表现排名'] = df['综合表现'].rank(ascending=False, method='min').astype(int)\n",
    "\n",
    "print(\"排名结果:\")\n",
    "print(\"Ranking Results:\")\n",
    "df[['学生姓名', '总分', '总分排名', '综合表现', '综合表现排名']].sort_values('总分排名')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 📝 总结 Summary\n",
    "\n",
    "恭喜你完成了所有练习！现在你应该掌握了：\n",
    "\n",
    "Congratulations on completing all exercises! You should now have mastered:\n",
    "\n",
    "✅ `df.head()` - 数据预览\n",
    "✅ `df.info()` - 数据信息查看\n",
    "✅ `df.describe()` - 统计描述\n",
    "✅ 创建新列 - 数据转换和计算\n",
    "✅ 条件筛选和数据分析\n",
    "✅ 相关性分析\n",
    "\n",
    "**继续学习建议 Next Learning Steps:**\n",
    "1. 数据分组和聚合 (df.groupby())\n",
    "2. 数据合并和连接 (pd.merge(), pd.concat())\n",
    "3. 数据可视化 (matplotlib, seaborn)\n",
    "4. 时间序列分析\n",
    "5. 机器学习数据预处理"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}